# -*- coding: utf-8 -*-
# @Author: hunan
# @Date: 2024/12/3 14:24
# @Description:
import logging
import socket
from typing import List, TypeVar, Type

import mysql.connector
from mysql.connector import Error

from product_upload.util.basic.common_util import sleep_random_duration

logger = logging.getLogger(__name__)
T = TypeVar('T')

dbconfig = {
    "host": "93284ae99lb7.vicp.fun",
    "port": 15326,
    "user": "root",
    "password": "QLKJ2024RMDKJ...huNAN",
    "database": "kuajing"
}


def get_connection(host=dbconfig["host"], port=dbconfig["port"], user=dbconfig["user"], password=dbconfig["password"], database=dbconfig["database"]):
    if socket.gethostname().lower() in ["admin_pro"]:
        port = 3306
        host = "127.0.0.1"
    elif socket.gethostname().lower() in ["robot6", "robot7", "robot8", "robot9", "center","robot53"]:
        port = 3306
        host = "192.168.1.2"
    try_count = 3
    for i in range(try_count):
        try:
            connection = mysql.connector.connect(host=host, port=port, user=user, password=password, database=database)
            return connection
        except Error as e:
            if i < try_count - 1:
                sleep_random_duration(0.1, 0.5)
                continue
            return None


def db_list_by_page(table, fields, conditions, model_class: Type[T], page=1, page_size=20, order_field="id", order_type="ASC") -> List[T] | None:
    connection = get_connection()
    if not connection:
        return None
    cursor = None
    try:
        cursor = connection.cursor(buffered=True)
        sql_query = f"SELECT {fields} FROM {table} WHERE {conditions} ORDER BY {order_field} {order_type} LIMIT %s OFFSET %s;"
        offset = (page - 1) * page_size
        cursor.execute(sql_query, (page_size, offset))
        results = cursor.fetchall()
        if fields == "*":
            field_names = model_class.fields
        else:
            field_names = [field.strip() for field in fields.split(',')]
        # 将查询结果映射到模型类实例
        return [model_class.from_db(field_names, row) for row in results] if model_class and results else results
    except Error as e:
        logger.error(f"Error while executing query: {e}")
        return None
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()


def db_list_by_page_v2(sql_query, page=1, page_size=20) -> List[T] | None:
    connection = get_connection()
    if not connection:
        return None
    cursor = None
    try:
        cursor = connection.cursor(buffered=True)
        offset = (page - 1) * page_size
        sql_query = f"{sql_query}  LIMIT {page_size} OFFSET {offset};"
        cursor.execute(sql_query)
        results = cursor.fetchall()
        return results
    except Error as e:
        logger.error(f"Error while executing query: {e}")
        return None
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()

def db_list_by_page_v3(table, fields, conditions, model_class: Type[T], page=1, page_size=20, order_field="id", order_type="ASC") -> List[T] | None:
    connection = get_connection()
    if not connection:
        return None
    cursor = None
    try:
        cursor = connection.cursor(buffered=True)
        # 动态生成 WHERE 子句（conditions 为空或 None 时省略）
        where_clause = f" WHERE {conditions}" if conditions else ""
        sql_query = f"SELECT {fields} FROM {table}{where_clause} ORDER BY {order_field} {order_type} LIMIT %s OFFSET %s;"
        offset = (page - 1) * page_size
        cursor.execute(sql_query, (page_size, offset))
        results = cursor.fetchall()
        if fields == "*":
            field_names = model_class.fields
        else:
            field_names = [field.strip() for field in fields.split(',')]
        # 将查询结果映射到模型类实例
        return [model_class.from_db(field_names, row) for row in results] if model_class and results else results
    except Error as e:
        logger.error(f"Error while executing query: {e}")
        return None
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()

def db_get_one(table, conditions, model_class: Type[T]) -> T | None:
    connection = get_connection()
    if not connection:
        return None
    cursor = None
    try:
        cursor = connection.cursor(buffered=True)
        sql_query = f"SELECT * FROM {table} WHERE {conditions} ORDER BY id ASC LIMIT 1;"
        cursor.execute(sql_query)
        result = cursor.fetchone()
        return model_class.from_db(model_class.fields, result) if model_class and result else result
    except Error as e:
        logger.error(f"Error while executing query: {e}")
        return None
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()


def db_list_lock_and_delete(table, fields, conditions, model_class: Type[T], page=1, page_size=20) -> List[T] | None:
    connection = get_connection()
    if not connection:
        return None
    cursor = None
    try:
        cursor = connection.cursor(buffered=True)
        connection.start_transaction()
        sql_query_select = f"SELECT {fields} FROM {table} WHERE {conditions} ORDER BY id ASC LIMIT %s OFFSET %s FOR UPDATE;"
        offset = (page - 1) * page_size
        cursor.execute(sql_query_select, (page_size, offset))
        results = cursor.fetchall()
        if results:
            if fields == "*":
                field_names = model_class.fields
            else:
                field_names = [field.strip() for field in fields.split(',')]
            # 将查询结果映射到模型类实例
            model_instances = [model_class.from_db(field_names, row) for row in results]
            # 获取要删除的id
            ids_to_delete = [row[0] for row in results]
            placeholders = ', '.join(['%s'] * len(ids_to_delete))
            sql_query_delete = f"DELETE FROM {table} WHERE id IN ({placeholders});"
            cursor.execute(sql_query_delete, ids_to_delete)
            connection.commit()
            return model_instances  # 返回转换后的实例列表
        else:
            connection.commit()
            return None
    except Error as e:
        logger.error(f"Error while executing query: {e}")
        if connection:
            connection.rollback()
        return None
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()


def db_delete(table, conditions):
    connection = get_connection()
    if not connection:
        return None
    cursor = None
    try:
        cursor = connection.cursor()
        sql_query = f"DELETE FROM {table} WHERE {conditions};"
        cursor.execute(sql_query)
        connection.commit()
        return cursor.rowcount
    except Error as e:
        logger.error(f"Error while executing query: {e}")
        if connection:
            connection.rollback()
        return None
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()


def db_batch_insert(table, fields, data):
    connection = get_connection()
    if not connection:
        return None
    cursor = None
    try:
        cursor = connection.cursor()
        field_names = ', '.join(fields)
        placeholders = ', '.join(['%s'] * len(fields))
        insert_query = f"INSERT INTO {table} ({field_names}) VALUES ({placeholders})"
        cursor.executemany(insert_query, data)
        connection.commit()
    except mysql.connector.Error as err:
        connection.rollback()
        logger.error(f"Error: {err}")
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()


def db_get_count(table, conditions) -> int | None:
    connection = get_connection()
    if not connection:
        return 0
    cursor = None
    try:
        cursor = connection.cursor()
        query = f"SELECT COUNT(id) FROM {table} WHERE {conditions}"
        cursor.execute(query)
        result = cursor.fetchone()
        count = result[0] if result else 0
        return count
    except mysql.connector.Error as err:
        logger.error(f"Error: {err}")
        return 0
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()


def db_batch_update(table, fields, data):
    connection = get_connection()
    if not connection:
        return None
    cursor = None
    try:
        cursor = connection.cursor()
        field_names = fields[1:]
        set_clause = ', '.join([f"{field} = %s" for field in field_names])
        where_clause = f"{fields[0]} = %s"
        update_query = f"UPDATE {table} SET {set_clause} WHERE {where_clause}"
        update_data = []
        for row in data:
            update_data.append(tuple(row[1:] + [row[0]]))
        cursor.executemany(update_query, update_data)
        connection.commit()
    except mysql.connector.Error as err:
        connection.rollback()
        logger.error(f"Error: {err}")
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()


def db_batch_update_v2(sql_query_):
    connection = get_connection()
    if not connection:
        return None
    cursor = None
    try:
        cursor = connection.cursor()
        cursor.execute(sql_query_)
        connection.commit()
        return cursor.rowcount
    except Error as e:
        logger.error(f"Error while executing query: {e}")
        if connection:
            connection.rollback()
        return None
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()


def get_amazon_us_upload_and_lock_task(fields, model_class: Type[T]) -> T | None:
    connection = get_connection()
    if not connection:
        return None
    cursor = None
    try:
        cursor = connection.cursor(buffered=True)
        connection.start_transaction()
        sql_query = f"SELECT {fields} FROM amazon_us_upload WHERE status = 0 and shop not in (select shop from amazon_us_upload where status = 3) ORDER BY id ASC LIMIT 1 FOR UPDATE;"
        cursor.execute(sql_query)
        task = cursor.fetchone()
        if task:
            if model_class:
                task = model_class.from_db(model_class.fields, task)
            update_query = "UPDATE amazon_us_upload SET status = 3 WHERE id = %s;"
            id_ = task.id if model_class else task[0]
            cursor.execute(update_query, (id_,))
            connection.commit()
            return task
        return None
    except Error as e:
        logger.error(f"Error while executing query: {e}")
        if connection:
            connection.rollback()
        return None
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()


if __name__ == '__main__':
    pass
